package com.comut.crud.dao;

import com.comut.crud.pojo.FindAndSet;
import com.comut.crud.pojo.driver.Driver;
import com.comut.crud.pojo.mobile.DriverDetail;
import com.comut.crud.pojo.mobile.DriverInfo;
import com.comut.crud.util.Util;
import com.comut.lib.connection.MongoDBConnectionManager;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;
import com.comut.lib.error.ErrorCodes;
import com.comut.lib.exception.BaseException;
import com.comut.lib.exception.ObjectFoundException;
import com.comut.lib.exception.ObjectNotFoundException;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;
import java.sql.*;

import java.util.HashMap;

import java.util.ResourceBundle;
import java.util.logging.Level;
import javax.jms.ConnectionFactory;
import javax.jms.DeliveryMode;
import javax.jms.JMSException;
import javax.jms.Message;
import javax.jms.MessageConsumer;
import javax.jms.MessageProducer;
import javax.jms.Queue;
import javax.jms.Session;
import javax.jms.TextMessage;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import org.apache.log4j.Logger;

/**
 *
 * @author asantos
 */
public class DriverDao {

    private static Logger _logger = Logger.getLogger(DriverDao.class.getName());
    private Connection _connection;
    private final MongoDBConnectionManager _mongodb;

    public DriverDao(Connection connection) {
        this._connection = connection;
        this._mongodb = null;
    }

    /**
	 * @author Paulo Fournier
     * Creates instance that uses a Connection and MongoDB Issue: IFL-1569
     * @param connection The mssql connection instance
	 * @param mongodb The mongodb connection manager
     */
    public DriverDao(Connection connection, MongoDBConnectionManager mongodb) {
        this._connection = connection;
        this._mongodb = mongodb;
    }

    /**
	 * @author Paulo Fournier
     * Issue: IFL-1569
     * Get the list of drivers from SQL that belong to a user (access to it)
     * @param userId The user id
     * @return HashMap with the list of drivers
     */
    private HashMap<Integer, DriverInfo> getDriversForUserIdSQL(int userId) {
        HashMap<Integer, DriverInfo> drivers = new HashMap();
        CallableStatement statement = null;
        ResultSet resultSet = null;
        String query = "{call [crud].[usp_getDriversForUserId](?)}";

        try {
            statement = _connection.prepareCall(query);
            statement.setInt(1, userId);
            resultSet = statement.executeQuery();

            while (resultSet.next()) {
                DriverInfo driver = new DriverInfo();
                int idDriver = resultSet.getInt("idDriver");
                driver.setIdDriver(idDriver);
                driver.setTag(resultSet.getString("tag"));
                driver.setName(resultSet.getString("name"));
                driver.setInternalNumber(resultSet.getString("internalNumber"));
                driver.setCardNumber(resultSet.getString("cardNumber"));
                drivers.put(idDriver, driver);
            }
        } catch (Exception ex) {
            java.util.logging.Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException ex) {
                    java.util.logging.Logger.getLogger(DriverDao.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                    java.util.logging.Logger.getLogger(DriverDao.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }

        return drivers;
    }

    /**
	 * @author Paulo Fournier
	 * Issue: IFL-1569
     * Gets drivers from user id from SQL server
     * @param userId The user id
     * @return DriverInfo[] The list of drivers and the total info data
     */
    public DriverInfo[] getDriversForUserId(int userId) {
        HashMap<Integer, DriverInfo> drivers = new HashMap();

        try {
            drivers = this.getDriversForUserIdSQL(userId);
            InitialContext initialContext = new InitialContext();
            ConnectionFactory connectionFactory = (ConnectionFactory) initialContext.lookup("/ConnectionFactory");
            javax.jms.Connection jmsConnection = (javax.jms.Connection) connectionFactory.createConnection();
            jmsConnection.start();

            Session jmsSession = (Session) jmsConnection.createSession(false, Session.AUTO_ACKNOWLEDGE);
            String taskId = com.comut.crud.util.Util.generateUUID();
            int numberSentMessages = sendJMSMessages(initialContext, jmsSession, drivers, taskId);
            waitJMSMessageReplies(initialContext, jmsSession, numberSentMessages, drivers, taskId);

            jmsSession.close();
            jmsConnection.close();
        } catch (Exception ex) {
            java.util.logging.Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
        }

        DriverInfo[] ret = new DriverInfo[drivers.size()];
        return drivers.values().toArray(ret);
    }

    public ResultSet getDriver(int id) {

        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_getDriver](?)}";
        try {
            stmt = _connection.prepareCall(query);
            stmt.setInt(1, id);
            rs = stmt.executeQuery();
        } catch (SQLException ex) {
            _logger.error("getDriver error");
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
        }

        return rs;
    }

    public int createDriver(Driver driver) {
        checkUniqueIbutton(driver.getIbutton(), -1);
        int toRet = -1;

        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_createDriver](?,?,?,?,?,?,?,?)}";
        String queryDriverGroup = "{call [crud].[usp_createDriver_group](?,?,?,?,?,?,?,?,?,?)}";
        try {

            stmt = _connection.prepareCall(query);
            stmt.setString(1, driver.getDr_code());
            stmt.setInt(2, driver.getType());
            stmt.setString(3, driver.getIssuing_authority());
            stmt.setInt(4, driver.getIssuing_state());
            stmt.setString(5, driver.getIssue_date());
            stmt.setString(6, driver.getCard_validation_date_begin());
            stmt.setString(7, driver.getCard_validation_date_end());
            stmt.setString(8, driver.getIbutton());
            rs = stmt.executeQuery();

            if (rs.next()) {
                toRet = rs.getInt(1);
            }

            if (driver.getDr_grp() != null) {
                stmt = _connection.prepareCall(queryDriverGroup);
                stmt.setInt(1, toRet);
                stmt.setInt(2, driver.getDr_grp().getId_group());
                stmt.setString(3, driver.getDr_grp().getInternal_number());
                stmt.setString(4, driver.getDr_grp().getName());
                stmt.setString(5, driver.getDr_grp().getTag());
                stmt.setString(6, driver.getDr_grp().getActivity_date_begin());
                stmt.setString(7, driver.getDr_grp().getActivity_date_end());
                stmt.setString(8, driver.getDr_grp().getPhone_personal());
                stmt.setString(9, driver.getDr_grp().getPhone_company());
                stmt.setString(10, driver.getDr_grp().getEmail());
                stmt.execute();

            }
        } catch (SQLException ex) {
            _logger.error("createDriver error", ex);
            if (ex.getErrorCode() == ErrorCodes.OBJECT_FOUND) {
                throw new ObjectFoundException(ex, ErrorCodes.LOGIN_FOUND);
            } else {
                throw new BaseException(ex, ex.getErrorCode());
            }
        } catch (Exception ex) {
            _logger.error("createDriver error", ex);
            throw new BaseException(ex, ErrorCodes.GENERAL_ERROR);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }

        return toRet;

    }

    /**
     * Update the driver table in SQLServer Also create or update [driver_group]
     *
     * @param driver Object with the where clause and the update clause
     */
    public void updateDriver(FindAndSet driver) {


        int driverId = -1;
        String ibutton = null;
        //update driver
        if (driver.getUpdate() != null && driver.getUpdate().length() > 0) {

            /**
             * If the driverIButton exists, check if is unique
             */
            String driverIButton = ResourceBundle.getBundle("dbFields").getString("driverIButton");
            driverId = Integer.parseInt(Util.cutFirstAndLastChars(driver.getFind().substring(driver.getFind().indexOf("=") + 1).trim()));
            String[] split = driver.getUpdate().split(",");
            for (int i = 0; i < split.length; i++) {
                String aux = split[i];
                if (aux.contains(driverIButton)) {
                    ibutton = aux.substring(aux.lastIndexOf("=") + 1).trim();
                    ibutton = Util.cutFirstAndLastChars(ibutton);
                    if (ibutton != null) {
                        checkUniqueIbutton(ibutton, driverId);
                    }
                }
            }


            String update = "UPDATE [cmt].[driver] SET " + driver.getUpdate();
            String where = " WHERE " + driver.getFind();

            PreparedStatement stmt = null;
            try {
                stmt = _connection.prepareStatement(update + where);
                int updateRows = stmt.executeUpdate();
                if (updateRows == 0) {
                    throw new ObjectNotFoundException("The driver does not exist", ErrorCodes.OBJECT_NOT_FOUND);
                }
            } catch (SQLException ex) {
                _logger.fatal("updateDriver error", ex);
                throw new BaseException(ex, ex.getErrorCode());
            } finally {
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (SQLException ignore) {
                    }
                }
            }
        }

        //setup driver_group
        //Check if the pair driver<->group already exists
        if (driver.getFasDriverGroup() != null) {
            if (driverId == -1) {
                driverId = Integer.parseInt(Util.cutFirstAndLastChars(driver.getFind().substring(driver.getFind().indexOf("=") + 1).trim()));
            }
            String findDriverGroup = driver.getFasDriverGroup().getFind();
            String findDriverUpdate = driver.getUpdateDriverGroup();
            int groupId = Integer.parseInt(Util.cutFirstAndLastChars(findDriverGroup.substring(findDriverGroup.indexOf("=") + 1).trim()));
            int id_DriverGroup = getDriverGroup(driverId, groupId);
            //if pair driver<->group already exists than update
            if (id_DriverGroup != -1) {


                String update = "UPDATE [cmt].[driver_group] SET " + driver.getFasDriverGroup().getUpdate();
                String where = " WHERE [id] =" + id_DriverGroup;

                PreparedStatement stmt = null;
                try {
                    stmt = _connection.prepareStatement(update + where);
                    int updateRows = stmt.executeUpdate();
                    if (updateRows == 0) {
                        throw new ObjectNotFoundException("The driver group does not exist", ErrorCodes.OBJECT_NOT_FOUND);
                    }
                } catch (SQLException ex) {
                    _logger.fatal("updateDriver error", ex);
                    throw new BaseException(ex, ex.getErrorCode());
                } finally {
                    if (stmt != null) {
                        try {
                            stmt.close();
                        } catch (SQLException ignore) {
                        }
                    }
                }


            } else {//create a new driverGroup
                CallableStatement cstmt = null;
                String queryDriverGroup = "{call [crud].[usp_createDriver_group](?,?,?,?,?,?,?,?,?,?)}";
                try {
                    JsonParser jp = new JsonParser();
                    JsonObject obj = null;
                    obj = (JsonObject) jp.parse(findDriverUpdate);

                    String driverGroupUpdateStr = ResourceBundle.getBundle("dbFields").getString("driverGroupUpdate");
                    String[] driverGroupUpdate = driverGroupUpdateStr.split(",");
                    cstmt = _connection.prepareCall(queryDriverGroup);
                    cstmt.setInt(1, driverId);
                    cstmt.setInt(2, groupId);
                    //if the object is null or the value of the attribute is null
                    if (obj.get(driverGroupUpdate[0]) == null || obj.get(driverGroupUpdate[0]).isJsonNull()) {
                        cstmt.setString(3, null);
                    } else {
                        cstmt.setString(3, obj.get(driverGroupUpdate[0]).getAsString());
                    }

                    if (obj.get(driverGroupUpdate[1]) == null || obj.get(driverGroupUpdate[1]).isJsonNull()) {
                        cstmt.setString(4, null);
                    } else {
                        cstmt.setString(4, obj.get(driverGroupUpdate[1]).getAsString());
                    }

                    if (obj.get(driverGroupUpdate[2]) == null || obj.get(driverGroupUpdate[2]).isJsonNull()) {
                        cstmt.setString(5, null);
                    } else {
                        cstmt.setString(5, obj.get(driverGroupUpdate[2]).getAsString());
                    }

                    if (obj.get(driverGroupUpdate[3]) == null || obj.get(driverGroupUpdate[3]).isJsonNull()) {
                        cstmt.setString(6, null);
                    } else {
                        cstmt.setString(6, obj.get(driverGroupUpdate[3]).getAsString());
                    }

                    if (obj.get(driverGroupUpdate[4]) == null || obj.get(driverGroupUpdate[4]).isJsonNull()) {
                        cstmt.setString(7, null);
                    } else {
                        cstmt.setString(7, obj.get(driverGroupUpdate[4]).getAsString());
                    }

                    if (obj.get(driverGroupUpdate[5]) == null || obj.get(driverGroupUpdate[5]).isJsonNull()) {
                        cstmt.setString(8, null);
                    } else {
                        cstmt.setString(8, obj.get(driverGroupUpdate[5]).getAsString());
                    }

                    if (obj.get(driverGroupUpdate[6]) == null || obj.get(driverGroupUpdate[6]).isJsonNull()) {
                        cstmt.setString(9, null);
                    } else {
                        cstmt.setString(9, obj.get(driverGroupUpdate[6]).getAsString());
                    }

                    if (obj.get(driverGroupUpdate[7]) == null || obj.get(driverGroupUpdate[7]).isJsonNull()) {
                        cstmt.setString(10, null);
                    } else {
                        cstmt.setString(10, obj.get(driverGroupUpdate[7]).getAsString());
                    }
                    cstmt.execute();
                } catch (SQLException ex) {
                    _logger.error("insert new driver group error", ex);
                    if (ex.getErrorCode() == ErrorCodes.OBJECT_FOUND) {
                        throw new ObjectFoundException(ex, ex.getErrorCode());
                    } else {
                        throw new BaseException(ex, ex.getErrorCode());
                    }
                } catch (Exception ex) {
                    _logger.error("insert new driver group error", ex);
                    throw new BaseException(ex, ErrorCodes.GENERAL_ERROR);
                } finally {

                    if (cstmt != null) {
                        try {
                            cstmt.close();
                        } catch (SQLException ignore) {
                        }
                    }
                }



            }
        }


    }

    /**
     * delete driver with the ID id, delete from [cmt].[driver_group] and
     * [cmt].[driver]
     *
     * @param id
     */
    public void deleteDriver(int id) {
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_deleteDriver](?)}";
        try {
            stmt = _connection.prepareCall(query);
            stmt.setInt(1, id);
            stmt.execute();
        } catch (SQLException ex) {
            _logger.error("deleteDriver error", ex);
            if (ex.getErrorCode() == ErrorCodes.OBJECT_NOT_FOUND) {
                throw new ObjectFoundException(ex, ex.getErrorCode());
            } else {
                throw new BaseException(ex, ex.getErrorCode());
            }
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }

    /**
     * check if there is other driver with the iButton, if exists throw an
     * exception
     *
     * @param iButton
     * @param driver_id
     */
    private void checkUniqueIbutton(String iButton, int driver_id) {
        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_getUniqueIButton](?,?)}";
        try {
            stmt = _connection.prepareCall(query);
            stmt.setString(1, iButton);
            String id = null;
            if (driver_id != -1) {
                id = "" + driver_id;
            }

            stmt.setString(2, id);

            rs = stmt.executeQuery();
            if (rs.next()) {
                throw new ObjectFoundException("The iButton already exists",ErrorCodes.IBUTTON_FOUND);
            }
        } catch (SQLException ex) {
            _logger.error("getUniqueIbutton error");
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }

    }

    /**
     * Function to get the id, if exists, of the pair driver_id<->driverGroup_id
     *
     * @param driver_id
     * @param driverGroup_id
     * @return id or -1 if not exists
     */
    private int getDriverGroup(int driver_id, int driverGroup_id) {
        ResultSet rs = null;
        int driverGroupExists = -1;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_getDriver_Group](?,?)}";
        try {
            stmt = _connection.prepareCall(query);
            stmt.setInt(1, driver_id);
            stmt.setInt(2, driverGroup_id);

            rs = stmt.executeQuery();
            if (rs.next()) {
                driverGroupExists = rs.getInt(1);
            }
        } catch (SQLException ex) {
            _logger.error("getDriverGroup error");
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }

        return driverGroupExists;

    }

    /**
	 * @author Paulo Fournier
     * Issue: IFL-1569
     * Send a Java Message Service with the driver for a post process queue
     * @param ic The message inicial context
     * @param jmsSession The java message service session
     * @param drivers The list of drivers to send
     * @return int The result
     * @throws JMSException
     * @throws NamingException
     */
    private int sendJMSMessages(InitialContext ic, Session jmsSession, HashMap<Integer, DriverInfo> drivers, String taskId) throws JMSException, NamingException {
        Queue driverInfoQueue = (Queue) ic.lookup("/queue/DriverInfoQueue");
        MessageProducer jmsMessageProducer = jmsSession.createProducer(driverInfoQueue);
        int nrMsgs = 0;
        int maxPriority = 9;
        int messageTimeToLiveMilliseconds = 10000;

        for (DriverInfo driverInfo : drivers.values()) {
            TextMessage message = jmsSession.createTextMessage(String.format("Driver id:%d", driverInfo.getIdDriver()));
            message.setIntProperty("driverId", driverInfo.getIdDriver());
            message.setLongProperty("milliTime", System.currentTimeMillis());
            message.setStringProperty("taskId", taskId);
            jmsMessageProducer.send(message, DeliveryMode.NON_PERSISTENT, maxPriority, messageTimeToLiveMilliseconds);
            nrMsgs++;
        }
        jmsMessageProducer.close();
        return nrMsgs;
    }

    /**
     * @author Paulo Fournier
     * Issue:IFL-1569
     * Receive a Java Message Service with the driver's information (JSM Queue reply)
     * @param ic The message inicial context
     * @param jmsSession The java message service session
     * @param nrMsgs The number of messages to receive
     * @param drivers The list of drivers
     * @throws NamingException
     * @throws JMSException
     */
    private void waitJMSMessageReplies(InitialContext ic, Session jmsSession, int nrMsgs, HashMap<Integer, DriverInfo> drivers, String taskId) throws NamingException, JMSException {
        String messageSelector = String.format("taskId = '%s' ", taskId);        
        Queue driverInfoReplyQueue = (Queue) ic.lookup("/queue/DriverInfoReplyQueue");
        java.util.logging.Logger.getLogger(this.getClass().getName()).log(Level.INFO , messageSelector );
        MessageConsumer consumer = jmsSession.createConsumer(driverInfoReplyQueue, messageSelector );
        int timeoutReceiveMilliseconds = 15000/nrMsgs;

        for (int msgIndex = 0; msgIndex < nrMsgs; msgIndex++) {
            Message reply = consumer.receive(timeoutReceiveMilliseconds);

            if (reply != null) {
                int mid = reply.getIntProperty("mid");
                int pos = reply.getIntProperty("pos");
                int state = reply.getIntProperty("state");
                int driverId = reply.getIntProperty("driverId");
                long lastDailyRest = reply.getLongProperty("lastDailyRest");
                long lastWeeklyRest = reply.getLongProperty("lastWeeklyRest");
                int reducedNumberOfRests = reply.getIntProperty("reducedNumberOfRests");
                int numberOfExtendedRestPeriods = reply.getIntProperty("numberOfExtendedRestPeriods");
                long drivingTimeCurrentJourney = reply.getLongProperty("drivingTimeCurrentJourney");
                long drivingTimeCurrentWeek = reply.getLongProperty("drivingTimeCurrentWeek");
                long drivingTimeCurrentForthnight = reply.getLongProperty("drivingTimeCurrentForthnight"); // current last 2 weeks

                DriverInfo di = drivers.get(driverId);
                di.setPosition(pos);
                di.setState(state);
                di.setVehicle(mid);
                di.setLastDailyRest(lastDailyRest);
                di.setLastWeeklyRest(lastWeeklyRest);
                di.setReducedNumberOfRests(reducedNumberOfRests);
                di.setNumberOfExtendedRestPeriods(numberOfExtendedRestPeriods);
                di.setDrivingTimeCurrentJourney(drivingTimeCurrentJourney);
                di.setDrivingTimeCurrentWeek(drivingTimeCurrentWeek);
                di.setDrivingTimeCurrentForthnight(drivingTimeCurrentForthnight);
            }
        }

        consumer.close();
    }

    /**
     * @author Paulo Fournier
     * Issue: IFL-1570
     * Get the detailed information about a driver
     * @param userId The user id
     * @param driverId The driver id
     * @return DriverDetail The driver detailed information
     */
    public DriverDetail getDriverDetail(int userId, int driverId) {
        DriverDetail ret = null;
        CallableStatement statement = null;
        ResultSet resultSet = null;
        String query = "{call [crud].[usp_getDriverDetail](?,?)}";

        try {
            statement = _connection.prepareCall(query);
            statement.setInt(1, userId);
            statement.setInt(2, driverId);
            resultSet = statement.executeQuery();

            if (resultSet.next()) {
                ret = new DriverDetail();
                ret.setBeginningOfActivity(resultSet.getDate("beginningOfActivity"));
                ret.setCompanyContact(resultSet.getString("companyContact"));
                ret.setEmail(resultSet.getString("email"));
                ret.setEmissionDate(resultSet.getDate("emissionDate"));
                ret.setEmissionState(resultSet.getString("emissionState"));
                ret.setEndOfActivity(resultSet.getDate("endOfActivity"));
                ret.setPersonalContact(resultSet.getString("personalContact"));
                ret.setStation(resultSet.getString("station"));
                ret.setValidationDate(resultSet.getDate("validationDate"));
            }
        } catch (Exception ex) {
            java.util.logging.Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException ex) {
                    java.util.logging.Logger.getLogger(DriverDao.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                    java.util.logging.Logger.getLogger(DriverDao.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }

        return ret;
    }
}
